-- visit_analysis
-- 数据访问统计（排名）

/**
    vid: rank, echart
    vtype: CT/PV/CN
    vnum: echart 数据

    -- 运行示例
    call visit_rank(null, null, null, null, null);
    call visit_rank('ct', null, null, null, null);
**/
drop procedure if exists `visit_rank`; -- 同同时执行报错
delimiter //
create procedure `visit_rank`(
    vtype varchar(25),
    vtime varchar(50),
    vtime_formate varchar(25),
    vpage int,
    vnum int     
)
this_sp:begin
    declare vsql varchar(2000);

    if vpage is null or vpage <0 then
        set vpage = 1;
    end if;
    if vnum is null or vnum < 0 then
        set vnum = 20;
    end if;
    if vtype is not null then
        set vtype = upper(vtype);
    end if;

    -- 数据类型判断
    if vtype = 'CT' then    -- city
        set vsql = "select replace(city, '市', '') as name, count(*) as value from sys_visit where annlyse_mk='Y' and city is not null and trim(city)<>'' ";
        if vtime is not null and vtime_formate is not null then
            set vsql = concat(
                vsql,
                " and date_format(mtime,'", vtime_formate, "')='", vtime, "' "
            );
        end if;        
        set vsql = concat(vsql, " group by replace(city, '市', '')");        
    elseif vtype = 'CN' then    -- country
        set vsql = "select country as name, count(*) as value from sys_visit where annlyse_mk='Y' and country is not null and trim(country)<>'' ";
        if vtime is not null and vtime_formate is not null then
            set vsql = concat(
                vsql,
                " and date_format(mtime,'", vtime_formate, "')='", vtime, "' "
            );
        end if;        
        set vsql = concat(vsql, ' group by country');
    else    -- province，行政省/直辖市
        set vsql = "select replace(replace(province, '市', ''), '省', '') as name, count(*) as value from sys_visit where annlyse_mk='Y' and province is not null and trim(province)<>'' ";
        if vtime is not null and vtime_formate is not null then
            set vsql = concat(
                vsql,
                " and date_format(mtime,'", vtime_formate, "')='", vtime, "' "
            );
        end if;        
        set vsql = concat(vsql, " group by replace(replace(province, '市', ''), '省', '')");
    end if;
    set vpage = (vpage - 1) * vnum;
    set vsql = concat(
        'select * from (',
        vsql,
        ') t order by t.value desc',
        ' limit ', vpage, ',', vnum
    )
    ;
    -- 调试语句
    -- select vsql;leave this_sp;
    set @sql = vsql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
end;